﻿CREATE PROCEDURE [dbo].[message_select_by_user]
    @userId int = 0,
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @login nvarchar(256) = null
AS
SELECT 
   (Select TOP(1) [Messages].[DateOfRead] FROM [dbo].[Messages] WHERE Messages.RecipientId = @userId Order by Messages.DateOfSend) as DateRead,
   (Select TOP(1) [Messages].[DateOfSend] FROM [dbo].[Messages] WHERE Messages.RecipientId = @userId Order by Messages.DateOfSend) as DateSend,
    Users.[Login],
    Users.[Avatar],
    Users.[UserId],
    (Select TOP(1) [Messages].[Message] FROM [dbo].[Messages] WHERE Messages.RecipientId = @userId Order by Messages.DateOfSend) as LastMessage
FROM [dbo].[Messages] inner join Users ON Users.UserId=Messages.SenderId
WHERE Messages.RecipientId = @userId and (@login IS NULL OR Users.Login LIKE + '%' + @login + '%')
    GROUP BY Users.Login,Users.Avatar,Users.UserId, Users.Email
ORDER BY
    CASE WHEN @column = 'DateOfSend' AND @direction = 1 THEN (Select TOP(1) [Messages].[DateOfRead] FROM [dbo].[Messages] WHERE Messages.RecipientId = @userId Order by Messages.DateOfSend)  END DESC,
    CASE WHEN @column = 'DateOfSend' AND @direction = 0 THEN (Select TOP(1) [Messages].[DateOfRead] FROM [dbo].[Messages] WHERE Messages.RecipientId = @userId Order by Messages.DateOfSend)  END ASC,
    CASE WHEN @column = 'Email' AND @direction = 1 THEN Users.Email END DESC,
    CASE WHEN @column = 'Email' AND @direction = 0 THEN Users.Email END ASC

    OFFSET ((@page-1)*@size) ROWS
    FETCH NEXT @size ROWS ONLY;

Declare @TotalCount int
SELECT @TotalCount = Count(DISTINCT Users.Login)
FROM [dbo].[Messages] inner join Users ON Users.UserId = Messages.SenderId
    where
       (@login IS NULL OR Users.Login LIKE '%' + @login + '%')

RETURN @TotalCount;
